import pymysql
class Mysql(object):
    #连接数据库
    def db_connect(self,host='localhost',user='python',password='******',database='python'):
        self.host=host
        self.user=user
        self.password=password
        self.database=database
        self.data_base = pymysql.connect(host=self.host, user=self.user, passwd=self.password, database=self.database)
        self.cursor = self.data_base.cursor(cursor=pymysql.cursors.DictCursor)
    def select_db(self,db):
        #选择一个表
        self.db=db
    def select_all(self,dict={},op1="=",op2="AND",order='desc',by='',limit=''):
        #从选择的表中获取数据（全部）
        sql = "SELECT * FROM {0} ".format(self.db)
        if dict:
            sql += "where "
            if op1!='=':
                sql += list(dict.keys())[0] + " " + op1 + " '%" + dict[list(dict.keys())[0]] + "%' "
                for i in list(dict.keys())[1:]:
                    sql += op2 + " " + i + " " + op1 + " '%" + dict[i] + "%'"
            else:
                sql += list(dict.keys())[0] + " " + op1 + " '" + dict[list(dict.keys())[0]] + "' "
                for i in list(dict.keys())[1:]:
                    sql += op2 + " " + i + " " + op1 + " '" + dict[i] + "'"
        if by!='':
            sql+=" order by "+by+" "+order
        if limit!='':
            sql+=" limit "+limit

        self.cursor.execute(sql)
        return self.cursor.fetchall()
    def select_one(self,dict={},op1="=",op2="AND",order='desc',by='',limit=''):
        #从选择的表中获取一个数据
        sql = "SELECT * FROM {0} ".format(self.db)
        if dict:
            sql += "where "
            if op1!='=':
                sql += list(dict.keys())[0] + " " + op1 + " '%" + dict[list(dict.keys())[0]] + "%' "
                for i in list(dict.keys())[1:]:
                    sql += op2 + " " + i + " " + op1 + " '%" + dict[i] + "%'"
            else:
                sql += list(dict.keys())[0] + " " + op1 + " '" + dict[list(dict.keys())[0]] + "' "
                for i in list(dict.keys())[1:]:
                    sql += op2 + " " + i + " " + op1 + " '" + dict[i] + "' "
        if by!='':
            sql+="order by "+by+" "+order+" "
        if limit!='':
            sql+="limit "+limit+" "
        self.cursor.execute(sql)
        return self.cursor.fetchone()
    def delete_one(self,dict={},op1="=",op2="AND",order='desc',by=''):
        #删除一个数据
        sql = "DELETE FROM {0} ".format(self.db)
        if dict:
            sql += "where "
            if op1 != '=':
                sql += list(dict.keys())[0] + " " + op1 + " '%" + dict[list(dict.keys())[0]] + "%' "
                for i in list(dict.keys())[1:]:
                    sql += op2 + " " + i + " " + op1 + " '%" + dict[i] + "%'"
            else:
                sql += list(dict.keys())[0] + " " + op1 + " '" + dict[list(dict.keys())[0]] + "' "
                for i in list(dict.keys())[1:]:
                    sql += op2 + " " + i + " " + op1 + " '" + dict[i] + "' "
        if by != '':
            sql += "order by " + by + " " + order + " "
        sql += "limit 1 "
        self.cursor.execute(sql)
        self.data_base.commit()

    def delete_all(self,dict={},op1="=",op2="AND",order='asc',by='',limit=''):
        #删除所有符合条件的数据
        sql = "DELETE FROM {0} ".format(self.db)
        if dict:
            sql += "where "
            if op1!='=':
                sql += list(dict.keys())[0] + " " + op1 + " '%" + dict[list(dict.keys())[0]] + "%' "
                for i in list(dict.keys())[1:]:
                    sql += op2 + " " + i + " " + op1 + " '%" + dict[i] + "%'"
            else:
                sql += list(dict.keys())[0] + " " + op1 + " '" + dict[list(dict.keys())[0]] + "' "
                for i in list(dict.keys())[1:]:
                    sql += op2 + " " + i + " " + op1 + " '" + dict[i] + "'"
        if by!='':
            sql+=" order by "+by+" "+order
        if limit!='':
            sql+=" limit "+limit
        self.cursor.execute(sql)
        return self.cursor.fetchall()
    def insert_data_one(self,dict={}):
        #插入一个数据（以字典形式传入）
        title=list(dict.keys())[0]
        data="'"+dict[title]+"'"
        for key in list(dict.keys())[1:]:
            title=title+','+key
            data=data+",'"+dict[key]+"\'"
        sql="INSERT INTO "+self.db+" ("+title+") VALUES ("+data+")"
        self.cursor.execute(sql)
        self.data_base.commit()
    def rollback(self):
        #回退
        self.data_base.rollback()